using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using DTO;
namespace DAO
{
    public class ThuocDAO
    {
        DataProvider Provider;
        public ThuocDAO()
        {
            Provider = new DataProvider();
        }

        public DataTable LayMaLoaiThuoc()
        {
            Provider.Connect();
            string str = "Select MaLoaiThuoc From THUOC Where MaLoaiThuoc >= all (Select MaLoaiThuoc From Thuoc)";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable LayBangThuoc()
        {
            Provider.Connect();
            string str = "select * from Thuoc";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable LayTenLoaiThuoc()
        {
            Provider.Connect();
            string str = "select TenLoaiThuoc from THUOC";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }


        public void ThemThuoc(ThuocDTO ThuocDto)
        {
            Provider.Connect();
            string str;
            str = "Insert into THUOC(TenLoaiThuoc, MaLoaiThuoc) values (?, ?)";

            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@TenLoaiThuoc", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoaiThuoc", OleDbType.WChar);

            cmd.Parameters["@TenLoaiThuoc"].Value = ThuocDto.TenLoaiThuoc;
            cmd.Parameters["@MaLoaiThuoc"].Value = ThuocDto.MaLoaiThuoc;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();

        }


        public void XoaThuoc(string MaLoaiThuoc)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Delete From THUOC Where MaLoaiThuoc = ?";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@MaLoaiThuoc", OleDbType.Char);
            cmd.Parameters["@MaLoaiThuoc"].Value = MaLoaiThuoc;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            Provider.Disconnect();
        }

        public void SuaThuoc(ThuocDTO ThuocDto)
        {
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Update Thuoc Set TenLoaiThuoc = ? Where MaLoaiThuoc = ? ";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            cmd.Parameters.Add("@TenLoaiThuoc", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoaiThuoc", OleDbType.WChar);

            cmd.Parameters["@TenLoaiThuoc"].Value = ThuocDto.TenLoaiThuoc;
            cmd.Parameters["@MaLoaiThuoc"].Value = ThuocDto.MaLoaiThuoc;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();
        }


        public DataTable TimKiemTheoMaLoaiThuoc(string MaLoaiThuoc)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select * From Thuoc Where MaLoaiThuoc = " + "'" + MaLoaiThuoc + "'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            cmd.Parameters.Add("@MaLoaiThuoc", OleDbType.WChar);
            cmd.Parameters["@MaLoaiThuoc"].Value = MaLoaiThuoc;

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }
    }
}
